package org.example.TOOL.Start;

import java.sql.*;
import java.util.logging.Logger;

public class pd_zyxxhzysjb {
    private static final Logger logger = Logger.getLogger(pd_zyxxhzysjb.class.getName());

    // 常量定义
    private static final String TARGET_TABLE = "pd_zyxxhzysjb1";
    private static final String SOURCE_TABLE = "base_zyjbqk";
    private static final int BATCH_SIZE = 1000;

    // 列名常量
    private static final String[] COLUMNS = {
            "id", "year", "code", "name", "zydm", "zymc",
            "xnzydm", "xnzymc", "ssdwh", "ssdwmc", "xz", "syxwml", "zszt"
    };

    // DDL语句模板
    private static final String CREATE_TABLE_SQL = """
        CREATE TABLE IF NOT EXISTS `%s` (
            `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
            `year` INT(4) DEFAULT NULL COMMENT '年份',
            `code` VARCHAR(255) DEFAULT NULL COMMENT '学校代码',
            `name` VARCHAR(255) DEFAULT NULL COMMENT '学校名称',
            `zydm` VARCHAR(255) DEFAULT NULL COMMENT '专业代码',
            `zymc` VARCHAR(255) DEFAULT NULL COMMENT '专业名称',
            `xnzydm` VARCHAR(255) DEFAULT NULL COMMENT '校内专业代码',
            `xnzymc` VARCHAR(255) DEFAULT NULL COMMENT '校内专业名称',
            `ssdwmc` VARCHAR(255) DEFAULT NULL COMMENT '所属单位名称',
            `ssdwh` VARCHAR(255) DEFAULT NULL COMMENT '所属单位号',
            `xz` VARCHAR(255) DEFAULT NULL COMMENT '学制',
            `syxwml` VARCHAR(255) DEFAULT NULL COMMENT '授予学位门类',
            `zszt` VARCHAR(255) DEFAULT NULL COMMENT '招生状态',
            PRIMARY KEY (`id`) USING BTREE,
            KEY `xnzydm` (`xnzydm`),
            KEY `xnzymc` (`xnzymc`),
            KEY `zymc` (`zymc`),
            KEY `zydm` (`zydm`),
            KEY `name` (`name`),
            KEY `idx_zy` (`xnzymc`,`xnzydm`),
            KEY `year` (`year`),
            KEY `code` (`code`),
            KEY `idx_zy_year_code_xnzydm` (`year`,`code`,`xnzydm`)
        ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 
        ROW_FORMAT=DYNAMIC COMMENT='准备数据-各个学校和校内专业信息表';
        """;

    // 查询语句模板
    private static final String SELECT_SQL = """
        SELECT %s FROM %s 
        WHERE `year` = ? AND deleted_flag = 0 
        GROUP BY `code`, xnzydm
        """;

    // 插入语句模板
    private static final String INSERT_SQL = """
        INSERT INTO %s (%s) VALUES (%s)
        """;

    public static void main(String[] args) {
        try (Connection conn = DriverManager.getConnection(Config.url, Config.user, Config.password)) {
            // 初始化表结构
            initializeTable(conn);


            // 执行数据迁移
            migrateData(conn);


            //插入的数据在数据库内创建一个表名和年份的组成的视图，方便查询
            createView(conn);
        } catch (SQLException e) {
            logger.severe("数据库操作失败: " + e.getMessage());
            e.printStackTrace();
        }
    }

    private static void createView(Connection conn) throws SQLException {
        String viewName = TARGET_TABLE + "_" + Config.year;
        String createViewSQL = String.format(
                "CREATE OR REPLACE VIEW %s AS SELECT * FROM %s WHERE year = %d",
                viewName, TARGET_TABLE, Config.year
        );
        try (Statement stmt = conn.createStatement()) {
            stmt.execute(createViewSQL);
            logger.info("视图创建完成: " + viewName);
        } catch (SQLException e) {
            logger.severe("视图创建失败: " + e.getMessage());
            e.printStackTrace();
        }
    }
    private static void initializeTable(Connection conn) throws SQLException {
        String formattedCreateTableSQL = String.format(CREATE_TABLE_SQL, TARGET_TABLE);

        try (Statement stmt = conn.createStatement()) {
            stmt.execute(formattedCreateTableSQL);
            logger.info("表结构初始化完成: " + TARGET_TABLE);
        }catch (SQLException e) {
            logger.severe("表结构初始化失败: " + e.getMessage());
            e.printStackTrace();
        }
    }

    private static void migrateData(Connection conn) throws SQLException {
        // 构建参数化查询
        String formattedSelectSQL = String.format(
                SELECT_SQL,
                String.join(", ", COLUMNS),
                SOURCE_TABLE
        );

        String placeholders = String.join(", ",
                java.util.stream.IntStream.rangeClosed(1, COLUMNS.length)
                        .mapToObj(i -> "?")
                        .toArray(String[]::new)
        );

        String formattedInsertSQL = String.format(
                INSERT_SQL,
                TARGET_TABLE,
                String.join(", ", COLUMNS),
                placeholders
        );

        try (PreparedStatement selectStmt = conn.prepareStatement(formattedSelectSQL);
             PreparedStatement insertStmt = conn.prepareStatement(formattedInsertSQL)) {

            // 设置查询参数
            selectStmt.setInt(1, Config.year);

            ResultSet rs = selectStmt.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();

            // 批处理插入
            int batchCount = 0;
            while (rs.next()) {
                for (int i = 0; i < COLUMNS.length; i++) {
                    insertStmt.setObject(i + 1, rs.getObject(COLUMNS[i]));
                }
                insertStmt.addBatch();

                if (++batchCount % BATCH_SIZE == 0) {
                    executeBatch(insertStmt);
                }
            }
            executeBatch(insertStmt); // 插入剩余记录

            logger.info("数据迁移完成，总记录数: " + batchCount);
        }
    }

    private static void executeBatch(PreparedStatement stmt) throws SQLException {
        int[] results = stmt.executeBatch();
        logger.info("批处理执行成功，影响行数: " + results.length);
    }
}